# -*- coding: utf-8 -*-#
#-------------------------------------------------------------------------------
# 建立者:        黄周云  
# Name:         test02
# Description:  例14.11
# Author:       ASUS
# Date:         2019/4/2
#-------------------------------------------------------------------------------


from sqlalchemy import create_engine,MetaData,Table,Column,Integer,String,Float,exc,orm
from sqlalchemy.ext.declarative import declarative_base
mysql = 'mysql+mysqlconnector://root:980721@localhost:3306/school?charset=utf8'
engine = create_engine(mysql,encoding='utf-8',echo=False)
engine.connect()

metadata = MetaData(engine)
person = Table('person1',metadata,
               Column('id',Integer,primary_key=True),
               Column('name',String(30)),
               Column('age',Integer),
               Column('address',String(100)),
               Column('salary',Float))
metadata.create_all(engine)

Base = declarative_base()
class Person(Base):
    __tablename__ = 'person1'
    id = Column(Integer,primary_key=True)
    name = Column(String(30))
    age = Column(Integer)
    address = Column(String(100))
    salary = Column(Float)

Session = orm.sessionmaker(bind=engine)
session = Session()



session.query(Person).delete()
session.commit()

person1 = Person(id=10,name='Bill',age=30,address='地球',salary=1234)
person2 = Person(id=20,name='Mike',age=40,address='火星',salary=4321)
person3 = Person(id=30,name='John',age=50,address='氪星',salary=10000)
session.add(person1)
session.add(person2)
session.add(person3)
session.commit()
print('成功插入数据')

session.query(Person).filter(Person.name=='Mike').update({'address':'千里之城'})
query = session.query(Person).filter(Person.name=='John')
print(query)
person = query.scalar()
person.age = 12
person.salary = 5432
session.commit()
print('成功更新了记录')
#使用组合条件查询person1表中的记录
persons = session.query(Person).filter((Person.age>=10) & (Person.salary>=2000))
for person4 in persons:
    print('name','=',person4.name)
    print('age','=',person4.age)
    print('salary','=',person4.salary)

print(persons.first().name)
print(persons.offset(1).scalar().name)
session.delete(person2)
session.commit()

session.close()


